package com.winning.xffw.service.service.impl;

import com.winning.xffw.service.entity.vo.TableDataVo;
import com.winning.xffw.service.jdbc.JdbcTemplateRepository;
import com.winning.xffw.service.mapper.DataBaseMapper;
import com.winning.xffw.service.service.IDataBaseService;
import lombok.extern.slf4j.Slf4j;
import org.postgresql.copy.CopyManager;
import org.postgresql.core.BaseConnection;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.List;
import java.util.Map;

/**
 * @Author: shipengfei
 * @Description:
 * @Date: 2019-05-27 14:23
 */
@Service
@Slf4j
public class DataBaseServiceImpl implements IDataBaseService {

    @Resource
    private JdbcTemplateRepository jdbcTemplateRepository;

    @Autowired
    private DataBaseMapper dataBaseMapper;

    /**
     * 校验表是否存在
     *
     * @param tableName
     * @return
     */
    @Override
    public boolean checkTableIsExists(String tableName) {
        List<Map<String, Object>> list = dataBaseMapper.checkTableIsExists(tableName);
        return list.size() > 0;
    }

    /**
     * 创建临时表
     *
     * @param targetTableName
     * @param tempTableName
     * @param indexColumns
     */
    @Override
    public void createTable(String targetTableName, String tempTableName, List<String> indexColumns) {
        //创建临时表时先进行删除操作
        dataBaseMapper.dropTable(tempTableName);
        dataBaseMapper.createTable(targetTableName, tempTableName, String.join(",", indexColumns));
    }

    /**
     * csv数据copy至数据表
     *
     * @param filename
     * @param tableName
     * @param indexColumns
     * @param rowSize
     */
    @Override
    public long copyCsvData2Table(String filename, String tableName, List<String> indexColumns, int rowSize) {
        long successCount = 0;
        StringBuilder sqlStr = new StringBuilder();
        sqlStr.append("COPY ");
        sqlStr.append(tableName);
        sqlStr.append("(" + String.join(",", indexColumns) + ")");
        sqlStr.append(" FROM STDIN WITH csv header DELIMITER AS ',' LOG ERRORS ");
        sqlStr.append(" SEGMENT REJECT LIMIT " + rowSize + ";");
        try (FileInputStream fileInputStream = new FileInputStream(filename);
             Connection conn = jdbcTemplateRepository.getJdbcTemplate().getDataSource().getConnection()) {
            CopyManager copyManager = new CopyManager((BaseConnection) conn.getMetaData().getConnection());
            Long startTime = System.currentTimeMillis();
            successCount = copyManager.copyIn(sqlStr.toString(), fileInputStream);
            Long endTime = System.currentTimeMillis();
            log.info("数据表{}成功导入数据量:{}，总数据量：{}，耗时：{}ms",
                    tableName, successCount, rowSize, (endTime - startTime));
            return successCount;
        } catch (Exception e) {
            log.error("csv导入数据表异常！", e.getCause());
            throw new RuntimeException(e);
        }
    }

    /**
     * 根据临时表更新目标表数据状态
     *
     * @param targetTableName
     * @param tempTableName
     * @param indexColumns
     */
    @Override
    public void updateTargetTableFlag(String targetTableName, String tempTableName, List<String> indexColumns) {
        dataBaseMapper.updateTargetTableFlag(targetTableName, tempTableName, indexColumns);
    }

    /**
     * 数据写入csv文件
     *
     * @param filename
     * @param sqlStr
     */
    @Override
    public void copyTableData2Csv(String filename, String sqlStr) {
        StringBuilder sql = new StringBuilder();
        sql.append(" COPY ( ");
        sql.append(sqlStr);
        sql.append(" ) TO STDOUT WITH CSV HEADER; ");
        try (OutputStream outputStream = new FileOutputStream(filename);
             Connection conn = jdbcTemplateRepository.getJdbcTemplate().getDataSource().getConnection()) {
            CopyManager copyManager = new CopyManager((BaseConnection) conn.getMetaData().getConnection());
            outputStream.flush();
            Long startTime = System.currentTimeMillis();
            Long successCount = copyManager.copyOut(sql.toString(), outputStream);
            Long endTime = System.currentTimeMillis();
            log.info("copyOut: SQL:[{}]，导出数量:{}，耗时：{}ms，文件:{}",
                    sqlStr, successCount, (endTime - startTime), filename);
        } catch (Exception e) {
            log.error("copyOut导出异常！", e.getCause());
            throw new RuntimeException(e);
        }
    }

    /**
     * 通过临时表批量更新目标表
     *（同一链接中创建临时表、导入数据、操作目标数据）
     * @param filename
     * @param tableDataVo
     */
    @Override
    public void batchUpdateTargetTableByTemp(String filename, TableDataVo tableDataVo) {
        StringBuilder sqlStr = new StringBuilder();
        sqlStr.append("COPY ");
        sqlStr.append(tableDataVo.getTempTableName());
        sqlStr.append("(" + String.join(",", tableDataVo.getIndexColumns()) + ")");
        sqlStr.append(" FROM STDIN WITH csv header DELIMITER AS ',' LOG ERRORS ");
        sqlStr.append(" SEGMENT REJECT LIMIT " + tableDataVo.getDataRows().size() + ";");
        try (FileInputStream fileInputStream = new FileInputStream(filename);
             Connection conn = jdbcTemplateRepository.getJdbcTemplate().getDataSource().getConnection()) {
            // 1.创建临时表临时表前先删除
            try (PreparedStatement ps = conn.prepareStatement(toDropSql(tableDataVo.getTempTableName()))) {
                ps.executeUpdate();
            }
            // 2.创建临时表
            try (PreparedStatement ps = conn.prepareStatement(
                    toCreateSql(tableDataVo.getTempTableName(), tableDataVo.getTargetTableName(), tableDataVo.getIndexColumns()))) {
                ps.executeUpdate();
            }
            // 3.copy in 临时表
            CopyManager copyManager = new CopyManager((BaseConnection) conn.getMetaData().getConnection());
            Long startTime = System.currentTimeMillis();
            long successCount = copyManager.copyIn(sqlStr.toString(), fileInputStream);
            Long endTime = System.currentTimeMillis();
            log.info("数据表{}成功导入数据量:{}，总数据量：{}，耗时：{}ms",
                    tableDataVo.getTempTableName(), successCount, tableDataVo.getDataRows().size(), (endTime - startTime));
            // 4.通过临时表更新目标表记录
            try (PreparedStatement ps = conn.prepareStatement(
                    toUpdateSql(tableDataVo.getTempTableName(), tableDataVo.getTargetTableName(), tableDataVo.getIndexColumns()))) {
                ps.executeUpdate();
            }
            // 5.删除临时表
            try (PreparedStatement ps = conn.prepareStatement(toDropSql(tableDataVo.getTempTableName()))) {
                ps.executeUpdate();
            }
        } catch (Exception e) {
            log.error("csv导入数据表异常！", e.getCause());
            throw new RuntimeException(e);
        }
    }

    /**
     * 更新目标表sql
     *
     * @param tempTableName
     * @param targetTableName
     * @param indexColumns
     * @return
     */
    private String toUpdateSql(String tempTableName, String targetTableName, List<String> indexColumns) {
        StringBuilder updateSql = new StringBuilder();
        updateSql.append(" delete from ");
        updateSql.append(targetTableName).append(" tt ");
        updateSql.append(" where 1 = 1 ");
        updateSql.append(" and exists ( ");
        updateSql.append(" select 1 from  ");
        updateSql.append(tempTableName).append(" t ");
        updateSql.append(" where 1 = 1 ");
        for (String indexColumn : indexColumns) {
            updateSql.append(" and t.").append(indexColumn);
            updateSql.append("=");
            updateSql.append("tt.").append(indexColumn);
        }
        updateSql.append(" ); ");
        return updateSql.toString();
    }

    /**
     * create临时表sql
     *
     * @param tempTableName
     * @param targetTableName
     * @param indexColumns
     * @return
     */
    private String toCreateSql(String tempTableName, String targetTableName, List<String> indexColumns) {
        StringBuilder createSql = new StringBuilder();
        createSql.append(" create global temporary table ");
        createSql.append(tempTableName);
        createSql.append(" as  select ");
        createSql.append(String.join(",", indexColumns));
        createSql.append(" from ");
        createSql.append(targetTableName);
        createSql.append(" where 1 = 2; ");
        return createSql.toString();
    }

    /**
     * drop表sql
     *
     * @param tableName
     * @return
     */
    private String toDropSql(String tableName) {
        String dropSql = "DROP TABLE IF EXISTS " + tableName + ";";
        return dropSql;
    }

}
